﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;

namespace Portal.CMS    
{
    public class AdvImageBLL
    {
        public AdvImageCls CreateModel(string ActiveLanguage,string AdvImageId)
        {
            string Query = "select * from TableAdvImage where AdvImageId=@AdvImageId or left(AdvImageId,8)=@AdvImageId";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                AdvImageCls AdvImageCls = new AdvImageCls();
                AdvImageCls.AdvImageId = (string)dtResult.Rows[0]["AdvImageId"];
                AdvImageCls.Subject = (string)dtResult.Rows[0]["Subject"];
                try
                {
                    AdvImageCls.Subject = (string)dtResult.Rows[0][ActiveLanguage + "Subject"];
                }
                catch { }
                try
                {
                    AdvImageCls.Description = (string)dtResult.Rows[0][ActiveLanguage + "Description"];
                }
                catch { }
                try
                {
                    AdvImageCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                try
                {
                    AdvImageCls.belongSite = (string)dtResult.Rows[0]["belongSite"];
                }
                catch { }
             
                
                return AdvImageCls;
            }
        }

        public AdvImageItemCls CreateModelItem(
            string ConnectionString,
            string ActiveLanguage,
            string AdvImageItemId)
        {
            string Query = "select * from TableAdvImageItem where AdvImageItemId=@AdvImageItemId or left(AdvImageItemId,8)=@AdvImageItemId";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                AdvImageItemCls AdvImageItemCls = new AdvImageItemCls();
                AdvImageItemCls.AdvImageItemId = (string)dtResult.Rows[0]["AdvImageItemId"];
                AdvImageItemCls.AdvImageId = (string)dtResult.Rows[0]["fk_AdvImage_Id"];
                AdvImageItemCls.AdvImageItemName = "";
                if (dtResult.Rows[0][ActiveLanguage + "AdvImageItemName"] != DBNull.Value)
                {
                    AdvImageItemCls.AdvImageItemName = (string)dtResult.Rows[0][ActiveLanguage + "AdvImageItemName"];
                }
                
                try
                {
                    AdvImageItemCls.Url = (string)dtResult.Rows[0]["Url"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.Alias = (string)dtResult.Rows[0]["Alias"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.ContentHtml = (string)dtResult.Rows[0]["ContentHtml"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.Target = (string)dtResult.Rows[0]["Target"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.Description = (string)dtResult.Rows[0]["Description"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.ItemEntryDate = (DateTime)dtResult.Rows[0]["ItemEntryDate"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.FileImage = (string)dtResult.Rows[0]["FileImage"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.IsPublishing = (int)dtResult.Rows[0]["IsPublishing"];
                }
                catch { }

                try
                {
                    AdvImageItemCls.Owner = (string)dtResult.Rows[0]["Owner"];
                }
                catch { }
                return AdvImageItemCls;
            }
        }

        public AdvImageItemCls CreateModelItem(
            string ActiveLanguage, 
            string AdvImageItemId)
        {
            return CreateModelItem(Environments.ConnectionString, ActiveLanguage, AdvImageItemId);
        }



        public AdvImageItemCls CreateModelItemByAlias(
           string ConnectionString,
           string ActiveLanguage,
           string Alias)
        {
            string Query = "select * from TableAdvImageItem where Alias=@Alias";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Alias"].Value = Alias;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            if (dtResult.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                string AdvImageItemId = (string)dtResult.Rows[0]["AdvImageItemId"];
                return CreateModelItem(ConnectionString, ActiveLanguage, AdvImageItemId);
            }
        }

        public DataTable Reading(string belongSite)
        {
            string Query = "select * from TableAdvImage where belongSite='" + belongSite+"'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public string GetRealFile(string AdvImageItemId, ref string belongSite, ref string AdvImageId)
        {
            string Query = "select * from TableAdvImageItem where AdvImageItemId='" + AdvImageItemId + "'";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            string FileImage = "";
            try
            {
                FileImage = (string)dtResult.Rows[0]["FileImage"];
                belongSite = (string)dtResult.Rows[0]["belongSite"];
                AdvImageId = (string)dtResult.Rows[0]["fk_AdvImage_Id"];
            }
            catch { }
            return FileImage;
        }


        public DataTable ReadingAvailableAlbums(string belongSite,string AdvImageId)
        {
            string Query = "select * from TableAlbum where belongSite='" + belongSite + "' and AlbumId not in (select fk_Album_Id from TableAlbumItem where belongSite='"+belongSite+"' and fk_AdvImage_Id='" + AdvImageId + "')";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingAdvImageItems(string AdvImageId, string Keyword)
        {
            string Query = "select * from TableAdvImageItem where fk_AdvImage_Id='" + AdvImageId+"' ";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and AdvImageItemName like N'%" + Keyword + "%'";
            }
            Query+=" order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingAdvImageItemsForSync(string AdvImageId)
        {
            string Query = "select AdvImageItemId from TableAdvImageItem where fk_AdvImage_Id='" + AdvImageId + "' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingPublishingAdvImageItems(string AdvImageId)
        {
            string Query = "select * from TableAdvImageItem where IsPublishing=1 and fk_AdvImage_Id='" + AdvImageId + "' order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingForSyncFromTool(string ConnectionString, string belongSite)
        {
            string Query = "select AdvImageItemId from TableAdvImageItem where IsPublishing=1 and belongSite=@belongSite";
            SqlConnection sqlConnection = new SqlConnection(ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public string AddAdvImage(SqlTransaction sqlTransaction, string ActiveLanguage, string Subject, string Description, string belongSite)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string AdvImageId = System.Guid.NewGuid().ToString();
            string Query = "Insert into TableAdvImage(AdvImageId,belongSite,Subject,[" + ActiveLanguage + "Subject],[" + Environments.ActiveLanguage + "Description], owner) values (@AdvImageId,@belongSite,@Subject,@Subject,@Description,@owner)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;


            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;


            sqlCommand.Parameters.Add("@owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@owner"].Value = Environment.Environments.CurrentLoginUser.LoginName;

            try
            {
                sqlCommand.ExecuteNonQuery();
                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("AdvImage");
                    
                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("AdvImageId");
                    ds.Tables[0].Columns.Add("Subject");
                    ds.Tables[0].Columns.Add("Description");
                    ds.Tables[0].Columns.Add("belongSite");
                    ds.Tables[0].Columns.Add("Owner");

                  
                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage, AdvImageId,Subject,Description,belongSite,Environment.Environments.CurrentLoginUser.LoginName
                    });
                }

                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //     Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //     for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //     {
                //         string RetValue = WServices[iIndexService].AddAdvImages(Environments.SecurityCode, XmlData);
                //         if (RetValue.Trim().Length > 0)
                //         {
                //             throw new Exception("Webservice:" + RetValue);
                //         }
                //     }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
            return AdvImageId;
        }

        
        public void UpdateAdvImage(
            SqlTransaction sqlTransaction,
            string ActiveLanguage, 
            string AdvImageId, 
            string Subject,
            string Description)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableAdvImage set Subject=@Subject,[" + ActiveLanguage + "Subject]=@Subject,[" + ActiveLanguage + "Description]=@Description where AdvImageId=@AdvImageId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;

            try
            {
                sqlCommand.ExecuteNonQuery();


                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("AdvImage");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("AdvImageId");
                    ds.Tables[0].Columns.Add("Subject");
                    ds.Tables[0].Columns.Add("Description");

                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage, AdvImageId,Subject,Description
                    });
                }

             
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].UpdateAdvImages(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void SaveContentHtml(
           SqlTransaction sqlTransaction,
           string AdvImageItemId,
           string ContentHtml)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "Update TableAdvImageItem set ContentHtml=@ContentHtml where AdvImageItemId=@AdvImageItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

            sqlCommand.Parameters.Add("@ContentHtml", SqlDbType.NText);
            sqlCommand.Parameters["@ContentHtml"].Value = ContentHtml;
            try
            {
                sqlCommand.ExecuteNonQuery();
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SaveAdvImageItems(
            SqlTransaction sqlTransaction,
            string ActiveLanguage,
            string AdvImageId,
            AdvImageItemCls[] AdvImageItems)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = "";
            try
            {
                //doan nay call webservice
                DataSet ds = new DataSet("xml");

                if (Portal.Environment.Environments.UseFrontEndDatabase)
                {
                    ds.Tables.Add("AdvImage");
                    ds.Tables.Add("AdvImageItems");

                    ds.Tables[0].Columns.Add("ActiveLanguage");
                    ds.Tables[0].Columns.Add("AdvImageId");

                    ds.Tables[1].Columns.Add("AdvImageItemId");
                    ds.Tables[1].Columns.Add("AdvImageItemName");
                    ds.Tables[1].Columns.Add("Url");
                    ds.Tables[1].Columns.Add("Target");
                    ds.Tables[1].Columns.Add("IndexOrder");
                    ds.Tables[1].Columns.Add("Description");
                    
                    ds.Tables[0].Rows.Add(new object[]{
                        ActiveLanguage, AdvImageId
                    });
                }

                for (int iIndex = 0; iIndex < AdvImageItems.Length; iIndex++)
                {
                    if (AdvImageItems[iIndex].AdvImageItemName != null)
                    {
                        if (AdvImageItems[iIndex].AdvImageItemName.Trim().Length > 0)
                        {
                            Query = " Update TableAdvImageItem set AdvImageItemName=@AdvImageItemName,[" + ActiveLanguage + "AdvImageItemName]=@AdvImageItemName,Url=@Url,Target=@Target,IndexOrder=@IndexOrder,Description=@Description where AdvImageItemId=@AdvImageItemId";
                            SqlCommand sqlCommand = sqlConnection.CreateCommand();
                            sqlCommand.CommandText = Query;
                            sqlCommand.Transaction = sqlTransaction;
                            sqlCommand.Parameters.Clear();

                            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
                            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItems[iIndex].AdvImageItemId;

                            sqlCommand.Parameters.Add("@AdvImageItemName", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@AdvImageItemName"].Value = AdvImageItems[iIndex].AdvImageItemName;

                            sqlCommand.Parameters.Add("@Url", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Url"].Value = AdvImageItems[iIndex].Url;

                            sqlCommand.Parameters.Add("@Target", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Target"].Value = AdvImageItems[iIndex].Target;

                            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
                            sqlCommand.Parameters["@IndexOrder"].Value = AdvImageItems[iIndex].IndexOrder;


                            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
                            sqlCommand.Parameters["@Description"].Value = AdvImageItems[iIndex].Description;

                            sqlCommand.ExecuteNonQuery();

                            if (Portal.Environment.Environments.UseFrontEndDatabase)
                            {
                                ds.Tables[1].Rows.Add(new object[]{
                                       AdvImageItems[iIndex].AdvImageItemId,AdvImageItems[iIndex].AdvImageItemName,AdvImageItems[iIndex].Url,AdvImageItems[iIndex].Target,AdvImageItems[iIndex].IndexOrder.ToString(),AdvImageItems[iIndex].Description
                                   });
                            }
                        }
                    }
                }
                //if (Portal.Environment.Environments.UseFrontEndDatabase)
                //{
                //    string XmlData = ds.GetXml();
                //    Environment.FrontEndWebService.asmx.Service[] WServices=FrontendWebServiceController.CreateWebService();
                //    for (int iIndexService = 0; iIndexService < WServices.Length; iIndexService++)
                //    {
                //        string RetValue = WServices[iIndexService].SaveAdvImageItems(Environments.SecurityCode, XmlData);
                //        if (RetValue.Trim().Length > 0)
                //        {
                //            throw new Exception("Webservice:" + RetValue);
                //        }
                //    }
                //}
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        
        public void DeleteAdvImage(SqlTransaction sqlTransaction, string AdvImageId)
        {
            AdvImageCls AdvImageCls = new AdvImageBLL().CreateModel(Environments.ActiveLanguage, AdvImageId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            Environment.Permission.DeletePermission(sqlTransaction, AdvImageId);
            string Query =
                    " Delete from TableAlbumItem where fk_AdvImage_Id=@AdvImageId;" +
                    " Delete from TableAdvImageItem where fk_AdvImage_Id=@AdvImageId;"+
                    " Delete from TableAdvImage where AdvImageId=@AdvImageId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageId"].Value = AdvImageId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (AdvImageCls != null)
                {
                    SubjectDeleted = AdvImageCls.Subject;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableAdvImage","Nhóm ảnh", AdvImageId,SubjectDeleted);
                string PathDelete = Environments.WorkingPath + "\\Media\\" + Environments.PortalAlias + "\\AdvImage\\Images\\" + AdvImageCls.belongSite + "\\" + AdvImageId;
                try
                {
                    System.IO.Directory.Delete(PathDelete, true);
                }
                catch { }
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void DeleteAdvImageItem(SqlTransaction sqlTransaction, string AdvImageItemId)
        {
            AdvImageItemCls oAdvImageItemCls = new AdvImageBLL().CreateModelItem(Environments.ActiveLanguage, AdvImageItemId);
            string belongSite = "";
            string AdvImageId = "";
            string FileName = new AdvImageBLL().GetRealFile(AdvImageItemId, ref belongSite,ref AdvImageId);

            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            string Query =
                    " Delete from TableAdvImageItem where AdvImageItemId=@AdvImageItemId;";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

            try
            {
                sqlCommand.ExecuteNonQuery();
                string SubjectDeleted = "";
                if (oAdvImageItemCls != null)
                {
                    SubjectDeleted = oAdvImageItemCls.AdvImageItemName;
                }
                Environments.AddDeletedRecordForSync(sqlTransaction, "TableAdvImageItem","Ảnh quảng cáo", AdvImageItemId, SubjectDeleted);
                try
                {
                    string RealFile = Environments.WorkingPath + "\\Media\\" + Environments.PortalAlias + "\\AdvImage\\Images\\" + belongSite + "\\" + AdvImageId + "\\" + FileName;
                    System.IO.File.Delete(RealFile);
                }
                catch { }
        
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void SaveOneAdvImageItem(
            SqlTransaction sqlTransaction, 
            string ActiveLanguage,
            string AdvImageItemId, 
            string Alias,
            string AdvImageItemName, 
            string Url, 
            string Target, 
            string Description, 
            string IndexOrder)
        {
            AdvImageItemCls AdvImageItemCls = new AdvImageBLL().CreateModelItem(ActiveLanguage, AdvImageItemId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }
            string Query =
                    " Update TableAdvImageItem set Alias=@Alias,[" + ActiveLanguage + "AdvImageItemName]=@AdvImageItemName,Url=@Url,Target=@Target,Description=@Description,IndexOrder=@IndexOrder where AdvImageItemId=@AdvImageItemId;";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

            sqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Alias"].Value = Alias;


            sqlCommand.Parameters.Add("@AdvImageItemName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AdvImageItemName"].Value = AdvImageItemName;

            sqlCommand.Parameters.Add("@Url", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Url"].Value = Url;


            sqlCommand.Parameters.Add("@Target", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Target"].Value = Target;

            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;


            try
            {
                sqlCommand.ExecuteNonQuery();

               
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }



        public void SyncItemWithServer(string ActiveLanguage, AdvImageItemCls AdvImageItemCls)
        {
            
        }

        public void AddAdvImageToAlbum(
            SqlTransaction sqlTransaction, 
            string belongSite,
            string AdvImageId,
            string AlbumId,
            string Subject)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string AlbumItemId = System.Guid.NewGuid().ToString();
            string Query =
                    " Insert into TableAlbumItem(AlbumItemId, belongSite, Subject,[" + Environments.ActiveLanguage + "Subject], fk_Album_Id,fk_AdvImage_Id,IndexOrder,ShortId) values (@AlbumItemId, @belongSite, @Subject, @Subject, @fk_Album_Id,@fk_AdvImage_Id,@IndexOrder,@ShortId)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();

            sqlCommand.Parameters.Add("@AlbumItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AlbumItemId"].Value = AlbumItemId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;


            sqlCommand.Parameters.Add("@ActiveLanguage", SqlDbType.VarChar);
            sqlCommand.Parameters["@ActiveLanguage"].Value = Environments.ActiveLanguage;

            sqlCommand.Parameters.Add("@Subject", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Subject"].Value = Subject;

            sqlCommand.Parameters.Add("@fk_Album_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_Album_Id"].Value = AlbumId;

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = 0;

            sqlCommand.Parameters.Add("@fk_AdvImage_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_AdvImage_Id"].Value = AdvImageId;

            sqlCommand.Parameters.Add("@ShortId", SqlDbType.VarChar);
            sqlCommand.Parameters["@ShortId"].Value = AlbumItemId.Substring(0,8);


            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void AddAdvImageItem(
            SqlTransaction sqlTransaction,
            string belongSite,
            string AdvImageId,
            string UploadFile,
            string Alias,
            string AdvImageItemName,
            string Url,
            string Description)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string AdvImageItemId = System.Guid.NewGuid().ToString();
            string Query = " Insert into TableAdvImageItem(AdvImageItemId,belongSite,fk_AdvImage_Id,FileImage,AdvImageItemName,[" + Environments.ActiveLanguage + "AdvImageItemName], Url,Target,IndexOrder,ItemEntryDate,Description,Owner, Alias) values (@AdvImageItemId,@belongSite,@fk_AdvImage_Id,@FileImage,@AdvImageItemName,@AdvImageItemName, @Url,@Target,@IndexOrder,GetDate(),@Description,@Owner,@Alias)";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            string ln = new System.IO.FileInfo(UploadFile).Name;

            string Owner = Environments.CurrentLoginUser.LoginName;
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

            sqlCommand.Parameters.Add("@belongSite", SqlDbType.NVarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;

            sqlCommand.Parameters.Add("@FileImage", SqlDbType.NVarChar);
            sqlCommand.Parameters["@FileImage"].Value = ln;


            sqlCommand.Parameters.Add("@AdvImageItemName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AdvImageItemName"].Value = AdvImageItemName;


            sqlCommand.Parameters.Add("@Description", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Description"].Value = Description;

            sqlCommand.Parameters.Add("@fk_AdvImage_Id", SqlDbType.VarChar);
            sqlCommand.Parameters["@fk_AdvImage_Id"].Value = AdvImageId;

            sqlCommand.Parameters.Add("@Url", SqlDbType.VarChar);
            sqlCommand.Parameters["@Url"].Value = Url;

            sqlCommand.Parameters.Add("@Target", SqlDbType.VarChar);
            sqlCommand.Parameters["@Target"].Value = "_blank";

            sqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            sqlCommand.Parameters["@IndexOrder"].Value = 0;

            sqlCommand.Parameters.Add("@Owner", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Owner"].Value = Owner;

            sqlCommand.Parameters.Add("@Alias", SqlDbType.NVarChar);
            sqlCommand.Parameters["@Alias"].Value = Alias;

            try
            {
                sqlCommand.ExecuteNonQuery();
                
                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void UpdateAvatar(
            SqlTransaction sqlTransaction,
            string AdvImageItemId,
            string AvatarFileName,
            byte[] b)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableAdvImageItem set AvatarFileName=@AvatarFileName,Avatar=@Avatar where AdvImageItemId=@AdvImageItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            string ln = new System.IO.FileInfo(AvatarFileName).Name;

            string Owner = Environments.CurrentLoginUser.LoginName;
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

            sqlCommand.Parameters.Add("@AvatarFileName", SqlDbType.NVarChar);
            sqlCommand.Parameters["@AvatarFileName"].Value = ln;

            sqlCommand.Parameters.Add("@Avatar", SqlDbType.Image);
            sqlCommand.Parameters["@Avatar"].Value = b;


            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }

        public void SyncAdvImageItem(
           string ConnectionString,
           string AdvImageItemId,
           bool UseWsForDeploy,
           string ActiveLanguage,
           string PathRoot,
           string WorkingPath,
           string PortalAlias,
           string WsUrl,
           string WsSecurityCode)
        {
            AdvImageItemCls AdvImageItemCls = new AdvImageBLL().CreateModelItem(ConnectionString, ActiveLanguage, AdvImageItemId);
            DataSet ds = new DataSet();
            string Query = "";
            if (UseWsForDeploy)
            {
                Query =
                    " select * from TableAdvImage where AdvImageId in (select fk_AdvImage_Id from TableAdvImageItem where AdvImageItemId=@AdvImageItemId);" +
                    " select * from TableAdvImageItem where AdvImageItemId=@AdvImageItemId;";
                SqlConnection sqlConnectionCheck = new SqlConnection(ConnectionString);
                sqlConnectionCheck.Open();
                SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                sqlCommandCheck.CommandText = Query;
                sqlCommandCheck.Parameters.Clear();
                sqlCommandCheck.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
                sqlCommandCheck.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

                SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                sqlda.Fill(ds);

                ds.Tables[0].TableName = "TableAdvImage";
                ds.Tables[1].TableName = "TableAdvImageItem";

                byte[] oBytes1 = null;
                string FileImage = "";
                try
                {
                    FileImage = (string)ds.Tables["TableAdvImageItem"].Rows[0]["FileImage"];
                }
                catch { }
                if (FileImage.Trim().Length > 0)
                {
                    string LoadFileImage =
                        WorkingPath + "\\Media\\" + PortalAlias + "\\AdvImage\\Images\\" + AdvImageItemCls.belongSite + "\\" + AdvImageItemCls.AdvImageId + "\\" + FileImage;
                    if (new System.IO.FileInfo(LoadFileImage).Exists)
                    {
                        oBytes1 = Portal.Environment.Environments.GetBytesFromFile(LoadFileImage);
                    }
                }

                string XmlData = ds.GetXml();
                Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Environments.CreateAppWs(WsUrl).SyncData(
                    WsSecurityCode,
                    AdvImageItemId,
                    AdvImageItemCls.belongSite,
                    XmlData,
                    oBytes1,
                    null,
                    null,
                    Framework.AppWs.ESycDataType.AdvImageItem);
                if (oWsReturnParamCls.Error)
                {
                    throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
                }
            }
        }

        public void SyncAdvImageItem(string AdvImageItemId)
        {
            string WsUrl = Environments.GetConfigFromXml("AppWs");
            string WsSecurityCode = Portal.Environment.Environments.WsSecurityCode;
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            SyncAdvImageItem(
                Environments.ConnectionString,
                AdvImageItemId,
                UseWsForDeploy,
                Environments.ActiveLanguage,
                Environments.PathRoot,
                Environments.WorkingPath,
                Environments.PortalAlias,
                WsUrl,
                WsSecurityCode);
        }



        public void SyncAdvImage(string AdvImageId)
        {
            bool UseWsForDeploy = Environments.UseWsForDeploy;
            
            string Query = "";
            if (UseWsForDeploy)
            {
                AdvImageCls AdvImageCls = new AdvImageBLL().CreateModel(Environments.ActiveLanguage, AdvImageId);
                DataTable dtItems = ReadingAdvImageItemsForSync(AdvImageId);
                for (int iIndex = 0; iIndex < dtItems.Rows.Count; iIndex++)
                {
                    DataSet ds = new DataSet();
                    string AdvImageItemId = (string)dtItems.Rows[iIndex]["AdvImageItemId"];
                    Query =
                        " select * from TableAdvImage where AdvImageId in (select fk_AdvImage_Id from TableAdvImageItem where AdvImageItemId=@AdvImageItemId);" +
                        " select * from TableAdvImageItem where AdvImageItemId=@AdvImageItemId;";
                    SqlConnection sqlConnectionCheck = new SqlConnection(Environment.Environments.ConnectionString);
                    sqlConnectionCheck.Open();
                    SqlCommand sqlCommandCheck = sqlConnectionCheck.CreateCommand();
                    sqlCommandCheck.CommandText = Query;
                    sqlCommandCheck.Parameters.Clear();
                    sqlCommandCheck.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
                    sqlCommandCheck.Parameters["@AdvImageItemId"].Value = AdvImageItemId;

                    SqlDataAdapter sqlda = new SqlDataAdapter(sqlCommandCheck);
                    sqlda.Fill(ds);

                    ds.Tables[0].TableName = "TableAdvImage";
                    ds.Tables[1].TableName = "TableAdvImageItem";

                    byte[] oBytes1 = null;
                    string FileImage = "";
                    try
                    {
                        FileImage = (string)ds.Tables["TableAdvImageItem"].Rows[0]["FileImage"];
                    }
                    catch { }
                    if (FileImage.Trim().Length > 0)
                    {
                        string LoadFileImage =
                            Portal.Environment.Environments.WorkingPath + "\\Media\\" + Portal.Environment.Environments.PortalAlias + "\\AdvImage\\Images\\" + AdvImageCls.belongSite + "\\" + AdvImageId + "\\" + FileImage;
                        if (new System.IO.FileInfo(LoadFileImage).Exists)
                        {
                            oBytes1 = Portal.Environment.Environments.GetBytesFromFile(LoadFileImage);
                        }
                    }

                    string XmlData = ds.GetXml();
                    Framework.AppWs.WsReturnParamCls oWsReturnParamCls = Portal.Environment.Environments.CreateAppWs().SyncData(
                        Portal.Environment.Environments.WsSecurityCode,
                        AdvImageItemId,
                        AdvImageCls.belongSite,
                        XmlData,
                        oBytes1,
                        null,
                        null,
                        Framework.AppWs.ESycDataType.AdvImageItem);
                    if (oWsReturnParamCls.Error)
                    {
                        throw new Exception("WS: " + oWsReturnParamCls.ErrorMsg);
                    }
                }
            }
        }

        public void Publishing(SqlTransaction sqlTransaction, string AdvImageItemId)
        {
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableAdvImageItem set IsPublishing=1 where AdvImageItemId=@AdvImageItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;


            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }


        public void UnPublishing(SqlTransaction sqlTransaction, string AdvImageItemId)
        {
            AdvImageItemCls AdvImageItemCls = new AdvImageBLL().CreateModelItem(Environments.ActiveLanguage, AdvImageItemId);
            bool HasTrans = sqlTransaction != null;
            SqlConnection sqlConnection = null;
            if (HasTrans == false)
            {
                sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
                sqlConnection.Open();
                sqlTransaction = sqlConnection.BeginTransaction();
            }
            else
            {
                sqlConnection = sqlTransaction.Connection;
            }

            string Query = " Update TableAdvImageItem set IsPublishing=0 where AdvImageItemId=@AdvImageItemId";
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Transaction = sqlTransaction;
            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.Add("@AdvImageItemId", SqlDbType.VarChar);
            sqlCommand.Parameters["@AdvImageItemId"].Value = AdvImageItemId;


            try
            {
                sqlCommand.ExecuteNonQuery();

                if (HasTrans == false)
                {
                    sqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasTrans == false)
                {
                    sqlTransaction.Rollback();
                }
                throw (ex);
            }
        }
    }
}
